using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BeeldBank.Data;

namespace BeeldBank.Data.Sql
{
    /// <summary>
    /// OrderBase - Provides Data Access methods for Order.
    /// </summary>
    /// <remarks>Generated class: DO NOT MODIFY!</remarks>
    public class OrderBase
    {
        /// <summary>
        /// Initializes a new instance of BeeldBank.Data.Base.OrderDaoBase.
        /// </summary>
        public OrderBase()
        {
        }

        #region Insert

        /// <summary>
        /// Insert a new record in the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to insert the ORDER table.</param>
        public static void Insert(DataRow dataRow)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                Insert(dataRow, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Insert a new record in the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to insert the ORDER table.</param>
        /// <param name="transaction">Transaction to execute the insert in.</param>
        public static void Insert(DataRow dataRow, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.InsertCommand = new SqlCommand();
            sqlDataAdapter.InsertCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
            sqlDataAdapter.InsertCommand.CommandText = "BEELDBANK_ORDER_INSERT";
            sqlDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Id
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@id";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // UserId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@userid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["UserId"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // OpdrachtNr
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@opdrachtnr";
            sqlParameter.SqlDbType = SqlDbType.NVarChar;
            sqlParameter.Value = dataRow["OpdrachtNr"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Gebruik
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@gebruik";
            sqlParameter.SqlDbType = SqlDbType.NText;
            sqlParameter.Value = dataRow["Gebruik"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // BestelDatum
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@besteldatum";
            sqlParameter.SqlDbType = SqlDbType.DateTime;
            sqlParameter.Value = dataRow["BestelDatum"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Opmerkingen
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@opmerkingen";
            sqlParameter.SqlDbType = SqlDbType.NText;
            sqlParameter.Value = dataRow["Opmerkingen"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // CreatedByColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@createdby";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["createdby"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // CreatedAtColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@createdat";
            sqlParameter.SqlDbType = SqlDbType.DateTime;
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // ModifiedByColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@modifiedby";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = "";
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // ModifiedAtColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@modifiedat";
            sqlParameter.SqlDbType = SqlDbType.DateTime;
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Version
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@version";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                if (sqlDataAdapter.InsertCommand.Parameters["@return"].Value == null)
                {
                    throw new ApplicationException("Unexpected error.", error);
                }
                switch ((int)sqlDataAdapter.InsertCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 515:
                        int posStart = error.Message.IndexOf("NULL into column '");
                        int posEnd = error.Message.IndexOf("'", posStart + 18);
                        throw new NullNotAllowedException("ORDER. " + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
                    case 2601:
                        throw new UniqueIndexException("ORDER");
                    case 50000:
                        throw new CustomException(error.Message);
                    default:
                        throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.InsertCommand.Parameters["@return"].Value).ToString() + ").", error);
                }
            }

            dataRow["Id"] = sqlDataAdapter.InsertCommand.Parameters["@id"].Value;

            dataRow["modifiedby"] = sqlDataAdapter.InsertCommand.Parameters["@modifiedby"].Value;
            dataRow["modifiedat"] = sqlDataAdapter.InsertCommand.Parameters["@modifiedat"].Value;
            dataRow["modifiedby"] = sqlDataAdapter.InsertCommand.Parameters["@modifiedby"].Value;
            dataRow["modifiedat"] = sqlDataAdapter.InsertCommand.Parameters["@modifiedat"].Value;
            dataRow["version"] = sqlDataAdapter.InsertCommand.Parameters["@version"].Value;

            //int returnValue = (int)sqlDataAdapter.InsertCommand.Parameters["@return"].Value;
        }

        #endregion

        #region Update

        /// <summary>
        /// Update a record in the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to insert the ORDER table.</param>
        public static void Update(DataRow dataRow)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                Update(dataRow, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Update a record in the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to update.</param>
        /// <param name="transaction">Transaction to execute the insert in.</param>
        public static void Update(DataRow dataRow, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = new SqlCommand();
            sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
            sqlDataAdapter.UpdateCommand.CommandText = "BEELDBANK_ORDER_UPDATE";
            sqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Id
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@id";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["Id"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // UserId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@userid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["UserId"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // OpdrachtNr
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@opdrachtnr";
            sqlParameter.SqlDbType = SqlDbType.NVarChar;
            sqlParameter.Value = dataRow["OpdrachtNr"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Gebruik
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@gebruik";
            sqlParameter.SqlDbType = SqlDbType.NText;
            sqlParameter.Value = dataRow["Gebruik"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // BestelDatum
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@besteldatum";
            sqlParameter.SqlDbType = SqlDbType.DateTime;
            sqlParameter.Value = dataRow["BestelDatum"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Opmerkingen
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@opmerkingen";
            sqlParameter.SqlDbType = SqlDbType.NText;
            sqlParameter.Value = dataRow["Opmerkingen"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // ModifiedByColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@modifiedby";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["modifiedby"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // ModifiedAtColumn
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Output;
            sqlParameter.ParameterName = "@modifiedat";
            sqlParameter.SqlDbType = SqlDbType.DateTime;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Version
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.InputOutput;
            sqlParameter.ParameterName = "@version";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["version"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                if (sqlDataAdapter.UpdateCommand.Parameters["@return"].Value == null)
                {
                    throw new ApplicationException("Unexpected error.", error);
                }
                switch ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 515:
                        int posStart = error.Message.IndexOf("NULL into column '");
                        int posEnd = error.Message.IndexOf("'", posStart + 18);
                        throw new NullNotAllowedException("ORDER." + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
                    case 2601:
                        throw new UniqueIndexException("ORDER");
                    case 50000:
                        throw new CustomException(error.Message);
                    default:
                        throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value).ToString() + ").", error);
                }
            }
            // Check if something was deleted.
            if ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value != 1)
            {
                sqlTransaction.Rollback();
                throw new ConcurrencyException("ORDER");
            }

            dataRow["modifiedby"] = sqlDataAdapter.UpdateCommand.Parameters["@modifiedby"].Value;
            dataRow["modifiedat"] = sqlDataAdapter.UpdateCommand.Parameters["@modifiedat"].Value;
            dataRow["version"] = sqlDataAdapter.UpdateCommand.Parameters["@version"].Value;

        }

        #endregion

        #region Delete

        /// <summary>
        /// Delete a record from the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to delete the ORDER table.</param>
        public static void Delete(DataRow dataRow)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                Delete(dataRow, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Delete a record from the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data to delete.</param>
        /// <param name="transaction">Transaction to execute the insert in.</param>
        public static void Delete(DataRow dataRow, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.DeleteCommand = new SqlCommand();
            sqlDataAdapter.DeleteCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.DeleteCommand.Transaction = sqlTransaction;
            sqlDataAdapter.DeleteCommand.CommandText = "BEELDBANK_ORDER_DELETE";
            sqlDataAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);

            // Id
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@id";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["Id"];
            sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);

            // Version
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@version";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = dataRow["version"];
            sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                switch ((int)sqlDataAdapter.DeleteCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 547:
                        int posStart = error.Message.IndexOf("table 'BEELDBANK_");
                        int length = "table 'BEELDBANK_".Length;
                        int posEnd = error.Message.IndexOf("'", posStart + length);
                        throw new DeleteConstraintException(error.Message.Substring(posStart + length, posEnd - posStart - length));
                    default:
                        throw new ApplicationException("Unexpected Database error.", error);
                }
            }
            // Check if something was deleted.
            if ((int)sqlDataAdapter.DeleteCommand.Parameters["@return"].Value != 1)
            {
                sqlTransaction.Rollback();
                throw new ConcurrencyException("ORDER");
            }
        }

        #endregion

        #region public static void Save(DataRow dataRow)
        /// <summary>
        /// Save changes from a DataRow to the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data save.</param>
        public static void Save(DataRow dataRow)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                Save(dataRow, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Save changes from a DataRow to the ORDER table.
        /// </summary>
        /// <param name="dataRow">DataRow containing the data save.</param>
        /// <param name="transaction">Transaction.</param>
        public static void Save(DataRow dataRow, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            switch (dataRow.RowState)
            {
                case DataRowState.Added:
                    Insert(dataRow, sqlTransaction);
                    break;
                case DataRowState.Modified:
                    Update(dataRow, sqlTransaction);
                    break;
                case DataRowState.Deleted:
                    Delete(dataRow, sqlTransaction);
                    break;
                default:
                    // Do nothing: DataRowState.Detached or DataRowState.Unchanged
                    break;
            }
        }
        #endregion

        #region void Save(DataTable dataTable)
        /// <summary>
        /// Save changes from a DataTable to the ORDER table.
        /// </summary>
        /// <param name="dataTable">DataTable containing the data save.</param>
        public static void Save(DataTable dataTable)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                Save(dataTable, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Save changes from a DataTable to the ORDER table.
        /// </summary>
        /// <param name="dataTable">DataTable containing the data save.</param>
        /// <param name="transaction">Transaction.</param>
        public static void Save(DataTable dataTable, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            DataTable insertTable = dataTable.GetChanges(DataRowState.Added);
            if (insertTable != null && insertTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in insertTable.Rows)
                {
                    Insert(dataRow, sqlTransaction);
                }
            }

            DataTable updateTable = dataTable.GetChanges(DataRowState.Modified);
            if (updateTable != null && updateTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in updateTable.Rows)
                {
                    Update(dataRow, sqlTransaction);
                }
            }

            DataTable deleteTable = dataTable.GetChanges(DataRowState.Deleted);
            if (deleteTable != null && deleteTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in deleteTable.Rows)
                {
                    Delete(dataRow, sqlTransaction);
                }
            }
            return;
        }
        #endregion

        #region public static DataTable New()
        /// <summary>
        /// Create an empty DataTable with columns for the ORDER table.
        /// </summary>
        public static DataTable New()
        {

            DataTable dataTable = new DataTable("ORDER");
            dataTable.Columns.Add("Id", System.Type.GetType("System.Int32"));
            dataTable.Columns.Add("UserId", System.Type.GetType("System.Int32"));
            dataTable.Columns.Add("OpdrachtNr", System.Type.GetType("System.String"));
            dataTable.Columns.Add("Gebruik", System.Type.GetType("System.String"));
            dataTable.Columns.Add("BestelDatum", System.Type.GetType("System.DateTime"));
            dataTable.Columns.Add("Opmerkingen", System.Type.GetType("System.String"));

            dataTable.Columns.Add("OrderId", System.Type.GetType("System.Int32"));
            dataTable.Columns.Add("CultureInfoId", System.Type.GetType("System.Int32"));
            dataTable.Columns.Add("createdby", System.Type.GetType("System.String"));
            dataTable.Columns.Add("createdat", System.Type.GetType("System.DateTime"));
            dataTable.Columns.Add("modifiedby", System.Type.GetType("System.String"));
            dataTable.Columns.Add("modifiedat", System.Type.GetType("System.DateTime"));
            dataTable.Columns.Add("version", System.Type.GetType("System.Int32"));

            return dataTable;
        }
        #endregion

        #region protected DataTable Open(string storedProcedureName, SqlParameter sqlParameter)

        /// <summary>
        /// Creates a DataTable with data from the ORDER table.
        /// </summary>
        /// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
        /// <param name="sqlParameter">Parameter that contains the argument for the Stored Procedure.</param>
        protected static DataTable Open(string storedProcedureName, SqlParameter sqlParameter)
        {
            return Open(storedProcedureName, sqlParameter, null);
        }

        /// <summary>
        /// Creates a DataTable with data from the ORDER table.
        /// </summary>
        /// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
        /// <param name="sqlParameters">Parameter array that contains the argument for the Stored Procedure.</param>
        protected static DataTable OpenParamArray(string storedProcedureName, SqlParameter[] sqlParameters)
        {
            return OpenParamArray(storedProcedureName, sqlParameters, null);
        }

        /// <summary>
        /// Fills a DataTable with data from the ORDER table.
        /// </summary>
        /// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
        /// <param name="sqlParameter">Parameter that contains the argument for the Stored Procedure.</param>
        /// <param name="dataTable">Datatable that is filled. If null, a new DataTable is created.</param>
        protected static DataTable Open(string storedProcedureName, SqlParameter sqlParameter, DataTable dataTable)
        {
            SqlParameter[] sqlParameters;

            if (sqlParameter != null)
            {
                sqlParameters = new SqlParameter[1];
                sqlParameters[0] = sqlParameter;
            }
            else
            {
                sqlParameters = new SqlParameter[0];
            }

            return OpenParamArray(storedProcedureName, sqlParameters, dataTable);
        }

        /// <summary>
        /// Fills a DataTable with data from the ORDER table.
        /// </summary>
        /// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
        /// <param name="sqlParameters">Parameter array that contains the arguments for the Stored Procedure.</param>
        /// <param name="dataTable">Datatable that is filled. If null, a new DataTable is created.</param>
        protected static DataTable OpenParamArray(string storedProcedureName, SqlParameter[] sqlParameters, DataTable dataTable)
        {
            // Setup Connection
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = new SqlCommand();
            sqlDataAdapter.SelectCommand.Connection = sqlConnection;
            sqlDataAdapter.SelectCommand.CommandText = storedProcedureName;
            sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            foreach (SqlParameter sqlParameter in sqlParameters)
            {
                sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);
            }

            // Fill dataset
            if (dataTable == null)
            {
                dataTable = new DataTable("Order");
            }
            sqlConnection.Open();
            try
            {
                sqlDataAdapter.Fill(dataTable);
            }
            catch (SqlException)
            {
                sqlConnection.Close();
                sqlConnection.Open();
                try
                {
                    sqlDataAdapter.Fill(dataTable);
                }
                catch
                {
                }
                return dataTable;
            }
            finally
            {
                sqlConnection.Close();
            }
            return dataTable;
        }
        #endregion

        #region Open methods

        /// <summary>
        /// Creates a DataTable containing all records and all fields from the ORDER table.
        /// </summary>
        public static DataTable Open()
        {
            return Open("BEELDBANK_ORDER_SELECT", null);
        }

        /// <summary>
        /// Fills a DataTable with all records and all fields from the ORDER table.
        /// </summary>
        public static DataTable Open(DataTable dataTable)
        {
            return Open("BEELDBANK_ORDER_SELECT", null, dataTable);
        }

        /// <summary>
        /// Creates a DataTable containing all records with the primary key column and the label columns from the ORDER table.
        /// </summary>
        public static DataTable OpenDropDown()
        {
            return Open("BEELDBANK_ORDER_SELECT_DROPDOWN", null);
        }

        /// <summary>
        /// Fills a DataTable with all records with the primary key column and the label columns from the ORDER table.
        /// </summary>
        public static DataTable OpenDropDown(DataTable dataTable)
        {
            return Open("BEELDBANK_ORDER_SELECT_DROPDOWN", null, dataTable);
        }
        /// <summary>
        /// Creates a DataTable containing the record specified by the primary key from the ORDER table.
        /// </summary>
        /// <param name="Id">Primary key value used to retrieve the record.</param>
        public static DataTable Open(int Id)
        {
            return Open(Id, null);
        }

        /// <summary>
        /// Fills a DataTable with the record specified by the primary key from the ORDER table.
        /// </summary>
        /// <param name="Id">Primary key value used to retrieve the record.</param>
        /// <param name="dataTable">DataTable to be filled.</param>
        public static DataTable Open(int Id, DataTable dataTable)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@id", SqlDbType.Int);
            sqlParameter.Value = Id;

            return Open("BEELDBANK_ORDER_SELECT_FROM_ID", sqlParameter, dataTable);
        }
        /// <summary>
        /// Creates a DataTable containing records from the BEELDBANK_ORDER table with UserId as specified in the argument.
        /// </summary>
        /// <param name="UserId">Argument that is used to retrieve the data.</param>
        public static DataTable OpenFromUserId(int UserId)
        {
            return OpenFromUserId(UserId, null);
        }

        /// <summary>
        /// Fills a DataTable with records from the BEELDBANK_ORDER table with UserId as specified in the argument.
        /// </summary>
        /// <param name="UserId">Argument that is used to retrieve the data.</param>
        /// <param name="dataTable">DataTable to be filled.</param>
        public static DataTable OpenFromUserId(int UserId, DataTable dataTable)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@userid", SqlDbType.Int);
            sqlParameter.Value = UserId;

            return Open("BEELDBANK_ORDER_SELECT_FROM_USERID", sqlParameter, dataTable);
        }
        /// <summary>
        /// Creates a DataTable containing records from the BEELDBANK_ORDER table with OpdrachtNr as specified in the argument.
        /// </summary>
        /// <param name="OpdrachtNr">Argument that is used to retrieve the data.</param>
        public static DataTable OpenFromOpdrachtNr(string OpdrachtNr)
        {
            return OpenFromOpdrachtNr(OpdrachtNr, null);
        }

        /// <summary>
        /// Fills a DataTable with records from the BEELDBANK_ORDER table with OpdrachtNr as specified in the argument.
        /// </summary>
        /// <param name="OpdrachtNr">Argument that is used to retrieve the data.</param>
        /// <param name="dataTable">DataTable to be filled.</param>
        public static DataTable OpenFromOpdrachtNr(string OpdrachtNr, DataTable dataTable)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@opdrachtnr", SqlDbType.NVarChar);
            sqlParameter.Value = OpdrachtNr;

            return Open("BEELDBANK_ORDER_SELECT_FROM_OPDRACHTNR", sqlParameter, dataTable);
        }

        #endregion

        #region Show methods
        /// <summary>
        /// Creates a DataTable containing all records and all fields from the ORDER table completed with the label fields of associated tables.
        /// </summary>
        public static DataTable Show()
        {
            return Open("BEELDBANK_ORDER_SELECT_SHOW", null);
        }
        /// <summary>
        /// Creates a DataTable containing the record specified by the primary key from the ORDER> table completed with the label fields of associated tables.
        /// </summary>
        /// <param name="Id">Primary key values used to retrieve the record.</param>
        public static DataTable Show(int Id)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@id", SqlDbType.Int);
            sqlParameter.Value = Id;

            return Open("BEELDBANK_ORDER_SELECT_SHOW_FROM_ID", sqlParameter);
        }
        /// <summary>
        /// Creates a DataTable containing records from the BEELDBANK_ORDER table with UserId as specified in the argument completed with the label fields of associated tables.
        /// </summary>
        /// <param name="UserId">Argument that is used to retrieve the data.</param>
        public static DataTable ShowFromUserId(int UserId)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@userid", SqlDbType.Int);
            sqlParameter.Value = UserId;

            return Open("BEELDBANK_ORDER_SELECT_SHOW_FROM_USERID", sqlParameter);
        }
        /// <summary>
        /// Creates a DataTable containing records from the BEELDBANK_ORDER table with OpdrachtNr as specified in the argument completed with the label fields of associated tables.
        /// </summary>
        /// <param name="OpdrachtNr">Argument that is used to retrieve the data.</param>
        public static DataTable ShowFromOpdrachtNr(string OpdrachtNr)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@opdrachtnr", SqlDbType.NVarChar);
            sqlParameter.Value = OpdrachtNr;

            return Open("BEELDBANK_ORDER_SELECT_SHOW_FROM_OPDRACHTNR", sqlParameter);
        }

        #endregion

        /// <summary>
        /// Creates a DataTable containing records from the ORDER table with BestelDatum as specified in the argument completed with the label fields of associated tables.
        /// </summary>
        /// <param name="BestelDatum">Argument that is used to retrieve the data.</param>
        public static DataTable ShowOrderList(DateTime BestelDatum)
        {
            SqlParameter[] sqlParameters;
            sqlParameters = new SqlParameter[1];

            SqlParameter sqlParameter1;
            sqlParameter1 = new SqlParameter("@besteldatum", SqlDbType.DateTime);
            sqlParameter1.Value = BestelDatum;
            sqlParameters[0] = sqlParameter1;

            return OpenParamArray("BEELDBANK_ORDER_SELECT_SHOW_ORDERLIST", sqlParameters, null);
        }
    }
}
